#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

ADS_TRAFFIC_PAGE_VIEW_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_app_ads.ads_traffic_page_view_report
SELECT
    dt,
    recent_days,
    province,
    city,
    page_view_count,
    user_count,
    session_count,
    avg_user_session_count,
    session_during_time,
    avg_session_during_time,
    bounce_count,
    bounce_count_rate
FROM jtp_app_ads.ads_traffic_page_view_report
UNION
SELECT
    '${data_date}' AS dt
    , 1 AS recent_days
    , province
    , city
    , sum(session_pv) AS page_view_count
    , count(DISTINCT device_id) AS user_count
    , count(session_id) AS session_count
    , round(count(session_id) / count(DISTINCT device_id), 2) AS avg_user_session_count
    , round(sum(session_during_time) / 1000 / 60, 2) AS session_during_time
    , round(avg(session_during_time) / 100 / 60, 2) AS avg_session_during_time
    , sum(if(session_pv = 1, 1, 0)) AS bounce_count
    , round(sum(if(session_pv = 1, 1, 0)) / count(session_pv), 4) AS bounce_count_rate
FROM jtp_app_dws.dws_event_log_session_agg
WHERE dt = '${data_date}' AND province IS NOT NULL AND city IS NOT NULL
GROUP BY province, city;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://192.168.88.101:10001 -n bwie -e "${ADS_TRAFFIC_PAGE_VIEW_REPORT_SQL}"
